package cn.piesat.scanning.dao;

import cn.piesat.scanning.dto.HtScanningMetadataDTO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.web.bind.annotation.PathVariable;

import java.util.LinkedList;
import java.util.List;

/**
 * 源数据表在数据库中的基本操作
 * 定义各种条件查询
 */
public interface HtScanningMetadataDAO extends JpaRepository<HtScanningMetadataDTO, String> {

    HtScanningMetadataDTO findByFileName(String fileName);

    @Query(value = "from HtScanningMetadataDTO H where H.fileName=:fileName ")
    List<HtScanningMetadataDTO> findAllByFileName(@Param("fileName")String fileName);

    @Query(value = "from HtScanningMetadataDTO where dataLevel = :dataLevel and sensor = :sensor and fileDate between :startTime and :endTime")
    List<HtScanningMetadataDTO> findBySensorAndDataLevelAndFileDateBetween(@Param("sensor") String sensor, @Param("dataLevel") String level, @Param("startTime") String startTime, @Param("endTime") String endTime);

    @Query(value = "select filePath from HtScanningMetadataDTO where dataLevel = :dataLevel and productIid = :productIid and fileType= :filetype and fileName like %:fileName% and fileDate >=:startTime and fileDate<:endTime")
    String[] listFileBySensorAndRangeTimeAndLevel(@Param("productIid") String productIid,  @Param("startTime") String startTime, @Param("endTime") String endTime,@Param("dataLevel") String level, @Param("filetype") String filetype, @Param("fileName") String fileName);

    @Query(value = "from HtScanningMetadataDTO where dataLevel = :dataLevel and bands = :bands and fileDate between :startTime and :endTime")
    List<HtScanningMetadataDTO> findByBandsAndDataLevelAndFileDateBetween(@Param("bands") String bands, @Param("dataLevel") String level, @Param("startTime") String startTime, @Param("endTime") String endTime);

    /**
     * 根据数据等级、产品标识列表、文件时间范围查询源数据信息列表
     *
     * @param dataLevel      数据等级
     * @param productIidList 产品标识列表
     * @param startTime      开始时间
     * @param endTime        结束时间
     * @return
     */
    List<HtScanningMetadataDTO> findByDataLevelAndProductIidInAndFileDateBetween(String dataLevel, List<String> productIidList, String startTime, String endTime);

    /**
     * 根据数据等级集合查询某个时间之前的源数据信息集合
     *
     * @param fileDate 时次
     * @param levels   数据等级集合
     * @return
     */
    List<HtScanningMetadataDTO> findByFileDateBeforeAndDataLevelIn(String fileDate, List<String> levels);

    /**
     * 根据文件名称列表查询
     *
     * @param fileNameList 文件名称列表
     * @return
     */
    List<HtScanningMetadataDTO> findByFileNameIn(List<String> fileNameList);

    /**
     * 根据时间范围查询文件名称集合
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @return
     */
    @Query("select fileName from HtScanningMetadataDTO where fileDate between :startTime and :endTime")
    List<String> findFileNameByDate(String startTime, String endTime);


    /**
     * 新版
     *
     * @param sensor
     * @param level
     * @param startTime
     * @param endTime
     * @param band
     * @return
     */
    List<HtScanningMetadataDTO> findBySensorAndDataLevelAndFileDateBetweenAndBands(String sensor, String level, String startTime, String endTime, String band);

    List<HtScanningMetadataDTO> findByProductIidAndBandsAndDataLevelAndLonAndLatAndFileDateBetween(String productId, String bands,
                                                                                                   String dataLevel, String lon,
                                                                                                   String lat, String leftTime,
                                                                                                   String endTime);

    @Query(nativeQuery = true, value = "select * from db_dms_scanning_metadata_info where FILE_DATE <= :date and " +
            " FILE_NAME REGEXP :regex")
    List<HtScanningMetadataDTO> regexAndDateRange(String date, String regex);

    List<HtScanningMetadataDTO> findByBandsInAndDataLevelAndFileDateBetween(List<String> bandsList, String dataLevel,
                                                                            String leftTime, String rightTime);

    List<HtScanningMetadataDTO> findByFileDateAfterAndDataLevelIn(String lessThenTime, List<String> levelList);

    @Query(value = "select VIEW_NUMBER from db_dms_scanning_metadata_info" +
            " where DATALEVEL like :dataLevel" +
            " and FILE_DATE >= :startTime " +
            " and FILE_DATE <= :endTime" +
            " group by VIEW_NUMBER", nativeQuery = true)
    List<String> groupByViewNumber(String dataLevel, String startTime, String endTime);

    List<HtScanningMetadataDTO> findAllByBandsInAndViewNumberAndDataLevel(List<String> bandList, String viewNumber, String dataLevel);

    List<HtScanningMetadataDTO> findAllBySensorInAndViewNumberAndDataLevel(List<String> bandList, String viewNumber, String dataLevel);

    List<HtScanningMetadataDTO> findBySensorAndViewNumberAndDataLevelAndFileDateBetween(String sensor, String viewNumber,
                                                                                        String dataLevel, String startTime,
                                                                                        String endTime);

    @Query(value = "from HtScanningMetadataDTO where dataLevel = :dataLevel and fileCatalog = :fileCatalog and fileDate between :startTime and :endTime")
    List<HtScanningMetadataDTO> listByFileCatalogAndDataLevelAndRangeTime(@Param("fileCatalog") String fileCatalog, @Param("dataLevel") String level, @Param("startTime") String startTime, @Param("endTime") String endTime);

    @Query(value = "from HtScanningMetadataDTO where dataLevel = :dataLevel and fileCatalog = :fileCatalog and fileDate =:fileDate")
    List<HtScanningMetadataDTO> findByFileCatalogAndDataLevelAndFileDate(String fileCatalog, String dataLevel, String fileDate);

    void deleteByTaskPlanIdIn(List<String> taskPlanIds);

    @Query(nativeQuery = true, value = "select * from db_dms_scanning_metadata_info where datalevel = ?2 and if(IFNULL(?1,'') !='',bands=?1,1=1) and  if(IFNULL(?3,'') !='',product_iid=?3,1=1) and file_date between ?4 and ?5")
    List<HtScanningMetadataDTO> listByBandsAndDataLevelAndProductIidAndRangeTime(@Param("bands") String bands, @Param("dataLevel") String dataLevel, @Param("productIdentify") String productIdentify, @Param("startTime") String startTime, @Param("endTime") String endTime);

    void deleteByFileDateIn(List<String> deleteFileDates);

    List<HtScanningMetadataDTO> findByFileDateInAndDataLevel(List<String> fileDates, String dataLevel);

    @Query(nativeQuery = true, value = "select TASK_PLAN_ID from db_dms_scanning_metadata_info where VIEW_NUMBER = ?1 GROUP BY VIEW_NUMBER")
    String findByViewNumberDistinct(String viewNumber);

    @Query(value = "from HtScanningMetadataDTO where  fileCatalog = :fileCatalog and fileDate between :startTime and :endTime")
    List<HtScanningMetadataDTO> listByFileCatalogAndRangeTime(@Param("fileCatalog") String fileCatalog, @Param("startTime") String startTime, @Param("endTime") String endTime);

    @Query(value = "from HtScanningMetadataDTO where  fileCatalog = :fileCatalog and fileDate =:fileDate")
    List<HtScanningMetadataDTO> findByFileCatalogAndFileDate(String fileCatalog, String fileDate);

    List<HtScanningMetadataDTO> findByFileCatalogAndFileDateBetween(String fileCatalog, String leftTime, String rightTime);

    /**
     * 根据标识，文件类别，级别，开始时间，截至时间查询源数据信息列表
     *
     * @param productIid  标识 in
     * @param fileCatalog 文件类别
     * @param dataLevel   级别
     * @param startTime   开始时间
     * @param endTime     截至时间
     * @return
     */
    @Query(value = "select * from db_dms_scanning_metadata_info where FILE_CATALOG=?1  and if(IFNULL(?2,'') !='',PRODUCT_IID in(?2),1=1)and if(?3!='',DATALEVEL=?3,1=1) and FILE_DATE between ?4 and ?5",nativeQuery = true)
    List<HtScanningMetadataDTO> findByFileCatalogAndProductIidInAndDataLevelAndFileDateBetween(String fileCatalog, List<String> productIid, String dataLevel, String startTime, String endTime);

    /**
     * 根据标识，文件类别，级别，时间查询源数据信息列表
     *
     * @param productIid  标识 in
     * @param fileCatalog 文件类别
     * @param dataLevel 级别
     * @param fileDate    时间
     * @return
     */
    @Query(value = "select * from db_dms_scanning_metadata_info where FILE_CATALOG=?1  and if(IFNULL(?2,'') !='',PRODUCT_IID in(?2),1=1) and if(?3!='',DATALEVEL=?3,1=1) and FILE_DATE =?4",nativeQuery = true)
    List<HtScanningMetadataDTO> findByFileCatalogAndProductIidInAndDataLevelAndFileDate(String fileCatalog, List<String> productIid, String dataLevel,String fileDate);

    @Query(value = "from HtScanningMetadataDTO where dataLevel = ?3 and fileDate between ?1 and ?2")
    List<HtScanningMetadataDTO> findByMetadata(String startTime, String endTime, String level);

    @Query(value = "from HtScanningMetadataDTO where satellite=?4 and productIid=?5 and  dataLevel = ?3 and fileDate between ?1 and ?2 order by fileDate DESC")
    List<HtScanningMetadataDTO> findByMetadata(String startTime, String endTime, String level, String satellite,String product);

    @Query(value = "from HtScanningMetadataDTO where satellite=?4 and productIid=?5 and region=?6 and productMode=?7 and fileType=?8 and dataLevel = ?3 and fileDate between ?1 and ?2 order by fileDate ASC")
    List<HtScanningMetadataDTO> findByMetadata(String startTime,String endTime,String level,String satellite,String product,String region,String productMode,String fileType);

    @Query(value = "from HtScanningMetadataDTO where satellite=?3 and productIid=?4 and  dataLevel = ?2 and fileDate =?1 and region = ?5")
    HtScanningMetadataDTO findOneByMetadata(String startTime, String level, String satellite,String product,String region);
    @Query(value = "from HtScanningMetadataDTO where satellite=?3 and productIid=?4 and  dataLevel = ?2 and fileDate =?1 and region = ?5 and fileName like %?6%")
    HtScanningMetadataDTO findOneByMetadata(String startTime, String level, String satellite,String product,String region,String fileName);
    @Query(value = "from HtScanningMetadataDTO where sensor=?2 and  satellite=?4 and productIid=?5 and  dataLevel = ?3 and fileDate =?1 and region = ?6 and fileName like %?7%")
    HtScanningMetadataDTO findGF1(String startTime,String sensor, String level, String satellite,String product,String region,String fileName);

    @Query(value = "from HtScanningMetadataDTO where satellite=?3 and productIid=?4 and  dataLevel = ?2 and fileDate =?1 and region = ?5 and orbitId=?7 and fileName like %?6%")
    HtScanningMetadataDTO findByOrbitId(String startTime, String level, String satellite,String product,String region,String fileName,String orbitId);

    @Query(value = "from HtScanningMetadataDTO where satellite=?3 and productIid=?4 and  dataLevel = ?2 and fileDate =?1 and region = ?5 and productMode=?6 and moduleIid=?7")
    HtScanningMetadataDTO findOneByMetadata(String startTime, String level, String satellite,String product,String region,String productMode,String moduleIid);
    @Query(value = "from HtScanningMetadataDTO where satellite=?3 and productIid=?4 and  dataLevel = ?2 and fileDate =?1 and region = ?5 and productMode=?6 and moduleIid=?7 and fileType=?8")
    HtScanningMetadataDTO findMetadataByParam(String startTime, String level, String satellite,String product,String region,String productMode,String moduleIid,String fileType);
    @Query(value = "from HtScanningMetadataDTO where satellite=?3 and productIid=?4 and  dataLevel = ?2 and fileDate =?1 and region = ?5 and productMode=?6 and moduleIid=?7 and fileType=?8 ")
    HtScanningMetadataDTO findOneByMetadata(String startTime, String level, String satellite,String product,String region,String productMode,String moduleIid,String fileType);
    @Query(value = "from HtScanningMetadataDTO where satellite=?4  and  dataLevel = ?3 and productIid=?5 and region=?6 and fileType=?7 and fileDate  between ?1 and ?2 ")
    List<HtScanningMetadataDTO> findByMetadata(String startTime, String endTime, String level, String satellite,String product,String region,String fileType);

    @Query(value = "from HtScanningMetadataDTO where satellite=?4 and productIid=?5 and  dataLevel = ?3  and region = ?6 and productMode=?7 and moduleIid=?8 and fileType=?9  and fileDate between ?1 and ?2 order by fileDate ")
    List<HtScanningMetadataDTO> findOneByMetadata (String startTime,String endTime, String level, String satellite,String product,String region,String productMode,String moduleIid,String fileType);
    @Query(value = "from HtScanningMetadataDTO where satellite=?4 and productIid=?5 and  dataLevel = ?3   and productMode=?6 and moduleIid=?7 and fileType=?8  and fileDate between ?1 and ?2 order by fileDate ")
    List<HtScanningMetadataDTO> findOneByMetadataNoRegion (String startTime,String endTime, String level, String satellite,String product,String productMode,String moduleIid,String fileType);

    @Query(value = "from HtScanningMetadataDTO where satellite=?4 and productIid=?5 and  dataLevel = ?3   and fileType=?6  and fileDate between ?1 and ?2 order by fileDate ")
    List<HtScanningMetadataDTO> findByMetadata (String startTime,String endTime, String level, String satellite,String product,String fileType);


    @Query(value = "from HtScanningMetadataDTO where satellite=?3 and productIid=?4 and  dataLevel = ?2 and fileDate =?1 and region = ?5 and fileType=?6 and moduleIid=?7")
    HtScanningMetadataDTO findMetaDataDto(String startTime, String level, String satellite,String product,String region,String fileType,String moduleIid);
    @Query(value = "from HtScanningMetadataDTO where satellite=?3 and productIid=?4 and  dataLevel = ?2 and fileDate =?1 and region = ?5 and fileType=?6 ")
    LinkedList<HtScanningMetadataDTO> findMetaDataDto(String startTime, String level, String satellite, String product, String region, String fileType);
    @Query(value = "from HtScanningMetadataDTO where satellite=?3  and  dataLevel = ?2 and productIid=?4 and region=?5 and fileType=?6 and fileDate  in ?1  ")
    List<HtScanningMetadataDTO> findByMetadata(List<String> startTime, String level, String satellite,String product,String region,String fileType);

    @Query(value = "from HtScanningMetadataDTO where satellite=?3 and productIid in ?4 and  dataLevel = ?2  and region = ?5 and productMode LIKE %?6 and moduleIid=?7 and fileType=?8  and fileDate = ?1  order by fileDate DESC")
    List<HtScanningMetadataDTO> findListLikeProductMode (String startTime, String level, String satellite,List<String> product,String region,String productMode,String moduleIid,String fileType);

    @Query(value = "from HtScanningMetadataDTO where satellite=?3 and productIid =?4 and  dataLevel =?2   and productMode =?5 and moduleIid=?6 and fileType=?7  and fileDate = ?1  order by fileDate DESC")
    List<HtScanningMetadataDTO> selectByNotRegion(String startTime, String level, String satellite,String product,String productMode,String moduleIid,String fileType);

    @Query(value = "from HtScanningMetadataDTO where satellite=?4 and productIid=?5 and  dataLevel = ?3  and region = ?6 and fileType=?7  and fileDate between ?1 and ?2 order by fileDate ")
    List<HtScanningMetadataDTO> findQxSite (String startTime,String endTime, String level, String satellite,String product,String region,String fileType);
    @Query(value = "from HtScanningMetadataDTO where satellite=?4 and productIid=?5 and  dataLevel = ?3  and region = ?6 and fileType=?7  AND sensor=?8 and fileDate between ?1 and ?2 order by fileDate ")
    List<HtScanningMetadataDTO> findBySensor (String startTime,String endTime, String level, String satellite,String product,String region,String fileType,String sensor);

    @Query(value = "SELECT region, filePath, MAX(fileDate) FROM HtScanningMetadataDTO WHERE dataLevel=:level and satellite=:satellite and  productIid=:product  AND fileType=:fileType AND fileDate BETWEEN :startTime AND :endTime and  fileDate LIKE '%090100%' GROUP BY region,filePath")
    List<Object[]> findRegionAndFilePath (String startTime,String endTime, String level, String satellite,String product,String fileType);





}
